<?php
/**
 * Created by jinqiang on 2024/9/18 13:13.
 * Copyright (c) 2024, Your Company. All rights reserved.
 */


namespace App\Http\Services\Tool;


use App\Http\Models\Mysql\StyleColourTable;
use App\Http\Models\Mysql\StyleTable;
use App\Http\Repository\CommonRepository;
use App\Http\Services\Banshi\DesignService;
use App\Http\Services\Soa\ReconciliationService;
use App\Http\Services\Soa\RepairService;
use Illuminate\Support\Facades\Cache;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class fileService
{
    protected $_cellName = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];

    protected $_currentRow = 1;

    protected $_titleStyle = [
        'font' => [
            'name' => '宋体', // 字体名称
            'size' => 14, // 字号
            'bold' => true, // 加粗
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER, // 水平居中
            'vertical' => Alignment::VERTICAL_CENTER, // 垂直居中
        ]
    ];

    protected $_cellWidth = 28;

    protected $_headerStyle = [
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'startColor' => [
                'argb' => Color::COLOR_BLACK,
            ],
        ],
        'font' => [
            'name' => '宋体', // 字体名称
            'size' => 11, // 字号
            'color' => [
                'argb' => Color::COLOR_WHITE,
            ],
        ]
    ];

    //绣花对账--pdf导出
    public function reconciliationPdf($id)
    {
        $reconciliationObj = (new ReconciliationService())->detail($id);
        //回货统计明细
        $returnHtml = "";
        foreach ($reconciliationObj['list'] as $item){
            $returnHtml .= "<tr style=\"border:1px solid black;\">
                <td style=\"border:1px solid black;\">{$item['processing_code']}</td>
                <td style=\"border:1px solid black;\">{$item['factory_contract_no']}</td>
                <td style=\"border:1px solid black;\">{$item['po_id']}</td>
                <td style=\"border:1px solid black;\">{$item['type_no']}</td>
                <td style=\"border:1px solid black;\">{$item['type_name']}</td>
                <td style=\"border:1px solid black;\">{$item['contract_machining_price']}</td>
                <td style=\"border:1px solid black;\">{$item['pay_machining_price']}</td>
                <td style=\"border:1px solid black;\">{$item['totalnum']}</td>
                <td style=\"border:1px solid black;\">{$item['total_cost_amount']}</td>
                <td style=\"border:1px solid black;\">{$item['emb_billing_amount']}</td>
            </tr>";
        }
        //额外费用
        $additionalHtml = "";
        foreach ($reconciliationObj['additional_list'] as $item){
            $additionalHtml .= "<tr style=\"border:1px solid black;\">
                <td style=\"border:1px solid black;\">{$item['factory_contract_no']}</td>
                <td style=\"border:1px solid black;\">{$item['processing_code']}</td>
                <td style=\"border:1px solid black;\">{$item['type_no']}</td>
                <td style=\"border:1px solid black;\">{$item['type_name']}</td>
                <td style=\"border:1px solid black;\">{$item['purpose']}</td>
                <td style=\"border:1px solid black;\">{$item['price']}</td>
                <td style=\"border:1px solid black;\">{$item['tax_rate_price']}</td>
                <td style=\"border:1px solid black;\">{$item['num']}</td>
                <td style=\"border:1px solid black;\">{$item['amount']}</td>
                <td style=\"border:1px solid black;\">{$item['remarks']}</td>
            </tr>";
        }
        //违约赔偿
        $fineHtml = "";
        foreach ($reconciliationObj['fine_list'] as $item){
            $fineHtml .= "<tr style=\"border:1px solid black;\">
                <td style=\"border:1px solid black;\">{$item['factory_contract_no']}</td>
                <td style=\"border:1px solid black;\">{$item['processing_code']}</td>
                <td style=\"border:1px solid black;\">{$item['type_no']}</td>
                <td style=\"border:1px solid black;\">{$item['type_name']}</td>
                <td style=\"border:1px solid black;\">{$item['amount']}</td>
                <td style=\"border:1px solid black;\">{$item['reason']}</td>
                <td style=\"border:1px solid black;\">{$item['remarks']}</td>
                <td style=\"border:1px solid black;\">{$item['createtime']}</td>
            </tr>";
        }
        //对账信息汇总
        $infoHtml = "";
        foreach ($reconciliationObj['info'] as $item){
            $infoHtml .= "<tr style=\"border:1px solid black;\">
                <td style=\"border:1px solid black;\">{$item['purchasein_amount']}</td>
                <td style=\"border:1px solid black;\">{$item['additional_amount']}</td>
                <td style=\"border:1px solid black;\">{$item['fine_amount']}</td>
                <td style=\"border:1px solid black;\">{$item['kaip_amount']}</td>
                <td style=\"border:1px solid black;\">{$item['pay_amount']}</td>
            </tr>";
        }
        $name = $reconciliationObj['factory_name']." ".$reconciliationObj['recon_time']." 入库结账对账单";
        $html = <<<EOD
<div style="width:100%">
    <div style=" text-align: center;font-size: 20px;font-weight: bold;margin-bottom: 20px;" >
        {$name}
    </div>
    <div style="margin-top:20px">
        <div>回货统计单(<span style="font-size: 12px;font-weight: bold">回货总金额：{$reconciliationObj['purchasein_amount']}</span>&nbsp;
        <span style="font-size: 12px;font-weight: bold">回货总数量：{$reconciliationObj['purchasein_num']}</span>)</div>
        <table style="border:1px solid black;width:100%;padding: 6px;min-height:100px;font-size:12px;text-align: center; border-collapse: collapse;">
            <tr style="border:1px solid black;">
                <th style="border:1px solid black;">生产加工单</th>
                <th style="border:1px solid black;">合同号</th>
                <th style="border:1px solid black;">采购单号</th>
                <th style="border:1px solid black;">款号</th>
                <th style="border:1px solid black;">款名</th>
                <th style="border:1px solid black;">大货绣花成本加价总价(含税)</th>
                <th style="border:1px solid black;">实付绣花成本加价总价(含税)</th>
                <th style="border:1px solid black;">数量</th>
                <th style="border:1px solid black;">贷款金额(含税)</th>
                <th style="border:1px solid black;">开票金额</th>
            </tr>
            {$returnHtml}
        </table>
    </div>
    <div style="margin-top: 20px">
        <div style="margin:2mm 0">额外费用</div>
        <table style="border:1px solid black;width: 100%;padding: 6px;min-height:100px;font-size:12px;text-align: center; border-collapse: collapse;">
            <tr style="border:1px solid black;">
                <th style="border:1px solid black;">合同号</th>
                <th style="border:1px solid black;">加工单号</th>
                <th style="border:1px solid black;">款号</th>
                <th style="border:1px solid black;">款名</th>
                <th style="border:1px solid black;">用途</th>
                <th style="border:1px solid black;">单价</th>
                <th style="border:1px solid black;">税额</th>
                <th style="border:1px solid black;">数量</th>
                <th style="border:1px solid black;">总价</th>
                <th style="border:1px solid black;">备注</th>
            </tr>
            {$additionalHtml}
        </table>
    </div>
    <div style="margin-top: 20px">
        <div style="margin:2mm 0">违约赔偿</div>
        <table style="border:1px solid black;width: 100%;padding: 6px;min-height:100px;font-size:12px;text-align: center; border-collapse: collapse;">
            <tr style="border:1px solid black;">
                <th style="border:1px solid black;">合同号</th>
                <th style="border:1px solid black;">加工单号</th>
                <th style="border:1px solid black;">款号</th>
                <th style="border:1px solid black;">款名</th>
                <th style="border:1px solid black;">违约赔偿</th>
                <th style="border:1px solid black;">赔偿事由</th>
                <th style="border:1px solid black;">备注</th>
                <th style="border:1px solid black;">赔偿申请日期</th>
            </tr>
            {$fineHtml}
        </table>
    </div>
    <div style="margin-top:20px">
        <div style="margin:2mm 0">对账信息汇总</div>
        <table style="border:1px solid black;width: 100%;padding: 6px;min-height:100px;font-size:12px;text-align: center; border-collapse: collapse;">
            <tr style="border:1px solid black;">
                <th style="border:1px solid black;">货款金额</th>
                <th style="border:1px solid black;">额外费用</th>
                <th style="border:1px solid black;">违约赔偿</th>
                <th style="border:1px solid black;">总开票金额</th>
                <th style="border:1px solid black;">总应付金额</th>
            </tr>
            {$infoHtml}
        </table>
    </div>
</div>
EOD;
        return [$name.".pdf", "对账单", $html];
    }

    //返修对账--pdf导出
    public function repairPdf($id)
    {
        $repairReconciliationObj = (new RepairService())->detail($id);
        //回货统计明细
        $returnHtml = "";
        foreach ($repairReconciliationObj['list'] as $item){
            $returnHtml .= "<tr style=\"border:1px solid black;\">
                <td style=\"border:1px solid black;\">{$item['processing_code']}</td>
                <td style=\"border:1px solid black;\">{$item['factory_contract_no']}</td>
                <td style=\"border:1px solid black;\">{$item['po_id']}</td>
                <td style=\"border:1px solid black;\">{$item['type_no']}</td>
                <td style=\"border:1px solid black;\">{$item['type_name']}</td>
                <td style=\"border:1px solid black;\">{$item['contract_price']}</td>
                <td style=\"border:1px solid black;\">{$item['pay_price']}</td>
                <td style=\"border:1px solid black;\">{$item['totalnum']}</td>
                <td style=\"border:1px solid black;\">{$item['cost_amount']}</td>
                <td style=\"border:1px solid black;\">{$item['billing_amount']}</td>
            </tr>";
        }
        //返修退款明细
        $repairHtml = "";
        foreach ($repairReconciliationObj['repair_list'] as $item){
            $repairHtml .= "<tr style=\"border:1px solid black;\">
                <td style=\"border:1px solid black;\">{$item['processing_code']}</td>
                <td style=\"border:1px solid black;\">{$item['jy_number']}</td>
                <td style=\"border:1px solid black;\">{$item['type_no']}</td>
                <td style=\"border:1px solid black;\">{$item['contract_machining_price']}</td>
                <td style=\"border:1px solid black;\">{$item['pay_machining_price']}</td>
                <td style=\"border:1px solid black;color: red\">-{$item['num']}</td>
                <td style=\"border:1px solid black;color: red\">-{$item['amount']}</td>
            </tr>";
        }
        //对账信息汇总
        $infoHtml = "";
        foreach ($repairReconciliationObj['info'] as $item){
            $infoHtml .= "<tr style=\"border:1px solid black;\">
                <td style=\"border:1px solid black;\">{$item['purchasein_amount']}</td>
                <td style=\"border:1px solid black;\">{$item['repair_amount']}</td>
                <td style=\"border:1px solid black;\">{$item['pay_amount']}</td>
            </tr>";
        }
        $name = $repairReconciliationObj['factory_name']." ".$repairReconciliationObj['recon_time']." 入库结账对账单";
        $html = <<<EOD
<div style="width:100%">
    <div style=" text-align: center;font-size: 20px;font-weight: bold;margin-bottom: 20px;" >
        {$name}
    </div>
    <div style="margin-top:20px">
        <div>回货统计单(<span style="font-size: 12px;font-weight: bold">回货总金额：{$repairReconciliationObj['purchasein_amount']}</span>&nbsp;
        <span style="font-size: 12px;font-weight: bold">回货总数量：{$repairReconciliationObj['purchasein_num']}</span>)</div>
        <table style="border:1px solid black;width:100%;padding: 6px;min-height:100px;font-size:12px;text-align: center; border-collapse: collapse;">
            <tr style="border:1px solid black;">
                <th style="border:1px solid black;">生产加工单</th>
                <th style="border:1px solid black;">合同号</th>
                <th style="border:1px solid black;">采购通知单</th>
                <th style="border:1px solid black;">款号</th>
                <th style="border:1px solid black;">款名</th>
                <th style="border:1px solid black;">大货成衣成本加价总价(含税)</th>
                <th style="border:1px solid black;">实付成衣成本加价总价(含税)</th>
                <th style="border:1px solid black;">数量</th>
                <th style="border:1px solid black;">贷款金额(含税)</th>
                <th style="border:1px solid black;">开票金额</th>
            </tr>
            {$returnHtml}
        </table>
    </div>
    <div style="margin-top: 20px">
        <div style="margin:2mm 0">返修退款</div>
        <table style="border:1px solid black;width: 100%;padding: 6px;min-height:100px;font-size:12px;text-align: center; border-collapse: collapse;">
            <tr style="border:1px solid black;">
                <th style="border:1px solid black;">生产加工单</th>
                <th style="border:1px solid black;">采购单</th>
                <th style="border:1px solid black;">款号</th>
                <th style="border:1px solid black;">大货成衣成本加价总价(含税)</th>
                <th style="border:1px solid black;">实付成衣成本加价总价(含税)</th>
                <th style="border:1px solid black;">数量</th>
                <th style="border:1px solid black;">返修退款金额</th>
            </tr>
            {$repairHtml}
        </table>
    </div>
    <div style="margin-top:20px">
        <div style="margin:2mm 0">返修对账信息汇总</div>
        <table style="border:1px solid black;width: 100%;padding: 6px;min-height:100px;font-size:12px;text-align: center; border-collapse: collapse;">
            <tr style="border:1px solid black;">
                <th style="border:1px solid black;">货款金额</th>
                <th style="border:1px solid black;">返修金额</th>
                <th style="border:1px solid black;">总应付金额</th>
            </tr>
            {$infoHtml}
        </table>
    </div>
</div>
EOD;
        return [$name.".pdf", "返修对账单", $html];
    }

    //售罄率--excel导出
    public function salesRateExcel($cacheKey)
    {
        $salesList = Cache::get($cacheKey) ?: [];
        $name = "售罄率统计";
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet()->setTitle($name);
        //设置回货统计单
        $this->writeCell($sheet, [
            $name,
            $this->_titleStyle,
            $this->_headerStyle,
            ["规格编码", "销售数据", "下单数量", "售罄率(%)"],
            ["sku", "sale_num", "num", "rate"],
            $salesList
        ]);
        $writer = new Xlsx($spreadsheet);
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$name.'.xls"');
        header("Content-Disposition:attachment;filename=$name.xls");//attachment新窗口打印inline本窗口打印
        $writer->save('php://output');
    }
    //大货BOM--excel导出
    public function bomExcel($styleId, $isProduct = 1)
    {
        $styleObj = (new StyleTable())->where('id', $styleId)->first(['number'])->toArray();
        $bomList = (new DesignService())->detailMaterial($styleId, $isProduct);
        $styleColorList = (new StyleColourTable())->where('style_id', $styleId)->get()->toArray();
        $styleColorMap = array_column($styleColorList, 'colour', 'id');
        $data = [];
        foreach ($bomList as $bom){
            //属性
            $materielAttribute = "";
            $materielAttributeList = json_decode($bom['materiel_attribute'], true);
            foreach ($materielAttributeList as $item){
                $materielAttribute = $materielAttribute.(!empty($materielAttribute) ? ", ":"").$item['name'].":".$item['value'];
            }
            $item = [
                'comment' => $bom['comment'],
                'materiel_l' => $bom['materiel_major_category'],//物料类型
                'materiel_f' => $bom['materiel_minor_category'],//物料分类
                'materiel_n' => $bom['materiel_number'],//物料编码
                'materiel' => $bom['materiel'],//物料名称
                'part' => $bom['part'],//部位,
                'materiel_a' => $materielAttribute,//属性
                'materiel_u' => $bom['materiel_major_unit'],//单位
                'amount' => round($bom['style_bom_size_config']['amount'] ?? 0, 3),//用料详情
                'price' => round($bom['price'], 3),//大货单价
                'supplier' => $bom['supplier'],//供应商
                'supplier_c' => $bom['supplier_contact_name'],//联系人
                'supplier_p' => $bom['supplier_phone'],//联系方式
                'supplier_a' => $bom['supplier_address'],//地址
            ];
            foreach ($styleColorMap as $colorId => $label){
                $item[$colorId] = [
                    'code' => isset($bom['style_bom_colour_config'][$colorId][0]['code']) ? CommonRepository::getColor($bom['style_bom_colour_config'][$colorId][0]['code'])
                        : "#fff",
                    'value' => $bom['style_bom_colour_config'][$colorId][0]['value'] ?? ""
                ];
            }
            $data[] = $item;
        }
        $headList = ["物料类型", "物料分类", "物料编码", "物料名称", "部位", "属性", "单位", "用料详情", "大货单价", "备注", "供应商", "联系人", "联系方式", "地址"];
        array_splice($headList, 6, 0, array_values($styleColorMap));
        $headKeyList = ["materiel_l", "materiel_f", "materiel_n", "materiel", "part", "materiel_a", "materiel_u", "amount", "price", "comment", "supplier", "supplier_c",
            "supplier_p", "supplier_a"];
        array_splice($headKeyList, 6, 0, array_keys($styleColorMap));
        $name = ($isProduct ? "大货BOM--" : "物料清单--").$styleObj['number'];
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet()->setTitle($name);
        $this->_cellWidth = 20;
        //设置回货统计单
        $this->writeCell($sheet, [
            $name,
            $this->_titleStyle,
            $this->_headerStyle,
            $headList,
            $headKeyList,
            $data
        ]);
        $writer = new Xlsx($spreadsheet);
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$name.'.xls"');
        header("Content-Disposition:attachment;filename=$name.xls");//attachment新窗口打印inline本窗口打印
        $writer->save('php://output');
    }

    //对账单--excel导出
    public function reconciliationExcel($id)
    {
        $reconciliationObj = (new ReconciliationService())->detail($id);
        $name = $reconciliationObj['factory_name']." ".$reconciliationObj['recon_time']." 入库结账对账单";
        $isXH = $reconciliationObj['type'] == 1 ? true : false;

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet()->setTitle($isXH ? "绣花对账单" : "成衣对账单");
        $titleStyle = [
            'font' => [
                'name' => '宋体', // 字体名称
                'size' => 14, // 字号
                'bold' => true, // 加粗
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, // 水平居中
                'vertical' => Alignment::VERTICAL_CENTER, // 垂直居中
            ]
        ];
        $headerStyle = [
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => [
                    'argb' => Color::COLOR_BLACK,
                ],
            ],
            'font' => [
                'name' => '宋体', // 字体名称
                'size' => 11, // 字号
                'color' => [
                    'argb' => Color::COLOR_WHITE,
                ],
            ]
        ];
        $header = ["生产加工单", "合同号", "采购单号", "款号", "款名", "大货成衣成本加价总价(含税)", "实付成衣成本加价总价(含税)", "回货数量", "货款金额(含税)", "开票金额"];
        $column = ["processing_code", "factory_contract_no", "po_id", "type_no", "type_name", "contract_price", "pay_price", "totalnum",
            "cost_amount", "billing_amount"];
        if($isXH){
            $header = ["生产加工单", "合同号", "采购单号", "款号", "款名", "大货绣花成本加价总价(含税)", "实付绣花成本加价总价(含税)", "回货数量", "货款金额(含税)", "开票金额"];
            $column = ["processing_code", "factory_contract_no", "po_id", "type_no", "type_name", "contract_machining_price", "pay_machining_price", "totalnum",
                "total_cost_amount", "emb_billing_amount"];
        }
        //设置回货统计单
        $this->writeCell($sheet, [
            $reconciliationObj['factory_name']." ".$reconciliationObj['recon_time'].' 回货统计单',
            $titleStyle,
            $headerStyle,
            $header,
            $column,
            $reconciliationObj['list']
        ]);
        //额外费用
        $this->writeCell($sheet, [
            '额外费用',
            $titleStyle,
            $headerStyle,
            ["合同号", "生产加工单", "款号", "款名", "用途", "单价", "税额", "数量", "总价", "备注"],
            ["factory_contract_no", "processing_code", "type_no", "type_name", "purpose", "price", "tax_rate_price", "num", "amount", "remarks"],
            $reconciliationObj['additional_list']
        ]);
        //违约赔偿
        $this->writeCell($sheet, [
            '违约赔偿',
            $titleStyle,
            $headerStyle,
            ["合同号", "生产加工单", "款号", "款名", "违约赔偿", "赔偿事由", "备注", "赔偿申请日期"],
            ["factory_contract_no", "processing_code", "type_no", "type_name", "amount", "reason", "remarks", "createtime"],
            $reconciliationObj['fine_list']
        ]);
        //对账信息汇总
        $this->writeCell($sheet, [
            '对账信息汇总',
            $titleStyle,
            $headerStyle,
            ["货款金额", "额外费用", "违约赔偿", "总开票金额", "总应付金额"],
            ["purchasein_amount", "additional_amount", "fine_amount", "kaip_amount", "pay_amount"],
            $reconciliationObj['info']
        ]);
        //工厂银行信息
        $this->writeCell($sheet, [
            '工厂银行信息',
            $titleStyle,
            $headerStyle,
            ["公司抬头", "开户行", "开户行号", "银行账户"],
            ["company_head", "bank_of_deposit", "opening_bank_no", "bank_account"],
            $reconciliationObj['bank']
        ]);
        $writer = new Xlsx($spreadsheet);
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$name.'.xls"');
        header("Content-Disposition:attachment;filename=$name.xls");//attachment新窗口打印inline本窗口打印
        $writer->save('php://output');
    }

    //绣花暂估对账单--excel导出
    public function xhzgExcel($id)
    {
        $xhzgReconciliationObj = (new ReconciliationService())->detailXhZg($id);
        $name = $xhzgReconciliationObj['factory_name']." ".$xhzgReconciliationObj['recon_time']." 入库结账对账单";

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet()->setTitle("绣花对账单(暂估)");
        $titleStyle = [
            'font' => [
                'name' => '宋体', // 字体名称
                'size' => 14, // 字号
                'bold' => true, // 加粗
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, // 水平居中
                'vertical' => Alignment::VERTICAL_CENTER, // 垂直居中
            ]
        ];
        $headerStyle = [
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => [
                    'argb' => Color::COLOR_BLACK,
                ],
            ],
            'font' => [
                'name' => '宋体', // 字体名称
                'size' => 11, // 字号
                'color' => [
                    'argb' => Color::COLOR_WHITE,
                ],
            ]
        ];
        //设置回货统计单
        $this->writeCell($sheet, [
            $xhzgReconciliationObj['factory_name']." ".$xhzgReconciliationObj['recon_time'].' 回货统计单',
            $titleStyle,
            $headerStyle,
            ["生产加工单", "款号", "款名", "sku", "绣花单价", "回货数量", "总金额"],
            ["processing_code", "type_no", "type_name", "sku_code", "contract_machining_price", "qty", "cost_amount"],
            $xhzgReconciliationObj['list']
        ]);
        $writer = new Xlsx($spreadsheet);
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$name.'.xls"');
        header("Content-Disposition:attachment;filename=$name.xls");//attachment新窗口打印inline本窗口打印
        $writer->save('php://output');
    }

    //返修对账--excel导出
    public function repairExcel($id)
    {
        $repairReconciliationObj = (new RepairService())->detail($id);
        $name = $repairReconciliationObj['factory_name']." ".$repairReconciliationObj['recon_time']." 入库结账对账单";

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet()->setTitle("采退返修对账单");
        $titleStyle = [
            'font' => [
                'name' => '宋体', // 字体名称
                'size' => 14, // 字号
                'bold' => true, // 加粗
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, // 水平居中
                'vertical' => Alignment::VERTICAL_CENTER, // 垂直居中
            ]
        ];
        $headerStyle = [
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => [
                    'argb' => Color::COLOR_BLACK,
                ],
            ],
            'font' => [
                'name' => '宋体', // 字体名称
                'size' => 11, // 字号
                'color' => [
                    'argb' => Color::COLOR_WHITE,
                ],
            ]
        ];
        //设置回货统计单
        $this->writeCell($sheet, [
            $repairReconciliationObj['factory_name']." ".$repairReconciliationObj['recon_time'].' 回货统计单',
            $titleStyle,
            $headerStyle,
            ["生产加工单", "合同号", "采购通知单", "款号", "款名", "大货成衣成本加价总价(含税)", "实付成衣成本加价总价(含税)", "数量", "货款金额(含税)"],
            ["processing_code", "factory_contract_no", "po_id", "type_no", "type_name", "contract_price", "pay_price", "totalnum", "cost_amount"],
            $repairReconciliationObj['list']
        ]);
        //采退返修退款统计
        $this->writeCell($sheet, [
            '采退返修退款统计',
            $titleStyle,
            $headerStyle,
            ["生产加工单", "采退返修单号", "款号", "业务类型", "大货成衣成本加价总价(含税)", "实付成衣成本加价总价(含税)", "数量", "退款金额"],
            ["processing_code", "jy_withdrawal_number", "type_no", "groups", "contract_machining_price", "pay_machining_price", "num", "amount"],
            $repairReconciliationObj['repair_list']
        ]);
        //对账信息汇总
        $this->writeCell($sheet, [
            '对账信息汇总',
            $titleStyle,
            $headerStyle,
            ["货款金额", "采退返修退款金额", "总应付金额"],
            ["purchasein_amount", "repair_amount", "pay_amount"],
            $repairReconciliationObj['info']
        ]);
        //工厂银行信息
        $this->writeCell($sheet, [
            '工厂银行信息',
            $titleStyle,
            $headerStyle,
            ["公司抬头", "开户行", "开户行号", "银行账户"],
            ["company_head", "bank_of_deposit", "opening_bank_no", "bank_account"],
            $repairReconciliationObj['bank']
        ]);
        $writer = new Xlsx($spreadsheet);
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$name.'.xls"');
        header("Content-Disposition:attachment;filename=$name.xls");//attachment新窗口打印inline本窗口打印
        $writer->save('php://output');
        exit;
    }

    //模块表格单元格写入
    public function writeCell(Object $sheet, $data = [])
    {
        list($title, $titleStyle, $headerStyle, $header, $columns, $list) = $data;
        $sheet->setCellValue("A{$this->_currentRow}", $title);
        $sheet->getStyle("A{$this->_currentRow}")->applyFromArray($titleStyle);
        $sheet->getRowDimension($this->_currentRow)->setRowHeight(30);
        $mergeCol = $this->_cellName[count($header)-1];
        $sheet->mergeCells("A{$this->_currentRow}:{$mergeCol}{$this->_currentRow}");
        $this->_currentRow += 1;
        foreach ($header as $index => $label){
            $sheet->setCellValue($this->_cellName[$index].($this->_currentRow), $label);
            $sheet->getStyle($this->_cellName[$index].($this->_currentRow))->applyFromArray($headerStyle);
            $sheet->getColumnDimension($this->_cellName[$index])->setWidth($this->_cellWidth);
        }
        $this->_currentRow += 1;
        $totalNum = 0;
        $totalAmount = 0;
        foreach ($list as $row => $item){
            if($title == '回货统计单'){
                $totalNum += (Int)$item['totalnum'];
                $totalAmount += (Double)$item['cost_amount'];
            }
            foreach ($columns as $cell => $column){
                if(preg_match("/^(大货BOM|物料清单)/", $title) && is_numeric($column)){
                    $label = $item[$column]['value'];
                }else{
                    $label = (in_array($column, ["opening_bank_no", "bank_account", "factory_contract_no"]) ? " ":"") . $item[$column];//避免长数字转换科学计算法
                }
                if($title == '采退返修退款统计'){
                    if(in_array($column, ["num", "amount"])){
                        $label = '-'.$label;
                    }
                    if($column == "groups"){
                        $label = $label == 1 ? "返厂维修" : "采购退货";
                    }
                }
                $sheet->setCellValue($this->_cellName[$cell].($this->_currentRow+$row), $label);
                if(preg_match("/^(大货BOM|物料清单)/", $title) && is_numeric($column)){//单元格填充颜色
                    $sheet->getStyle($this->_cellName[$cell].($this->_currentRow+$row))->applyFromArray([
                        'fill' => [
                            'fillType' => Fill::FILL_SOLID,
                            'startColor' => [
                                'argb' => str_replace("#", "", $item[$column]['code']),
                            ],
                        ],
                    ]);
                }
            }
        }
        $this->_currentRow += count($list);
        if($title == '回货统计单'){
            $sheet->setCellValue("A{$this->_currentRow}", "回货总金额：{$totalAmount}     回货总数量：{$totalNum}");
            $sheet->getStyle("A{$this->_currentRow}")->applyFromArray($titleStyle);
            $sheet->getRowDimension($this->_currentRow)->setRowHeight(30);
            $mergeCol = $this->_cellName[count($header)-1];
            $sheet->mergeCells("A{$this->_currentRow}:{$mergeCol}{$this->_currentRow}");
            $this->_currentRow += 1;
        }
        $this->_currentRow += 2;//空两行
    }
}
